package sk.bielyvlk.vlkgps.db;

import java.text.SimpleDateFormat;
import java.util.Date;

import sk.bielyvlk.vlkgps.VlkGps;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.location.Location;
import android.util.Log;

public class DbHelper {
	public static final String TAG = "vlkGPS.db.DatabaseHelper";

	private static final String DB_NAME = "db.sqlite";
	private static final int DB_VERSION = 1;

	public static final double DB_DEGREES_MULT = 1000000;
	public static final double DB_METERS_MULT = 1000;

	private SQLiteDatabase db = null;

	public synchronized SQLiteDatabase getReadableDatabase() {
		return db;
	}

	public synchronized SQLiteDatabase getWritableDatabase() {
		return db;
	}

	public void close() {
		db.close();
		db = null;
	}

	public DbHelper(Context context) {
		db = SQLiteDatabase.openDatabase(VlkGps.DB_DIR + "/" + DB_NAME, null, SQLiteDatabase.CREATE_IF_NECESSARY);
		if (db.getVersion() == 0) onCreate();
		db.setVersion(DB_VERSION);
	}

	public void onCreate() {
		Log.d(TAG, "creating new database...");
		db.execSQL("CREATE TABLE tracks (" +
				"_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"name TEXT, " +
				"description TEXT);");
		db.execSQL("CREATE TABLE trackpoints (" +
				"_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"track_id INTEGER NOT NULL, " +
				"lon INTEGER NOT NULL, " +
				"lat INTEGER NOT NULL, " +
				"alt INTEGER NOT NULL, " +
				"spd INTEGER NOT NULL, " +
				"acc INTEGER NOT NULL, " +
				"time INTEGER NOT NULL);");
		db.execSQL("CREATE TABLE waypoints (" +
				"_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
				"lon INTEGER NOT NULL, " +
				"lat INTEGER NOT NULL, " +
				"alt INTEGER NOT NULL, " +
				"acc INTEGER NOT NULL, " +
				"time INTEGER NOT NULL, " +
				"maxzoom INTEGER NOT NULL, " +
				"name TEXT, " +
				"show_name INTEGER, " +
				"description TEXT, " +
				"symbol TEXT);");
	}

	public void onUpgrade(int oldVersion, int newVersion) {
		Log.d(TAG, "upgrading database...");
	}
	
	public long insertTrack(String name, String description) {
		if (name == "") {
			SimpleDateFormat formater = new SimpleDateFormat("'Track_'yyyyMMdd_HHmm");
			name = formater.format(new Date());
		}
		ContentValues values = new ContentValues();
		values.put("name", name);
		values.put("description", description);
		return db.insert("tracks", null, values);
	}

	private boolean isTransaction = false;

	public void startTransaction() {
		if (isTransaction) return;
		db.execSQL("BEGIN;");
		isTransaction = true;
	}

	public void stopTransaction() {
		if (!isTransaction) return;
		db.execSQL("COMMIT;");
		isTransaction = false;
	}

	public long insertTrackpoint(double trackId, Location location) {
		startTransaction();
		ContentValues values = new ContentValues();
		values.put("track_id", trackId);
		values.put("lon", (int)(location.getLongitude() * DB_DEGREES_MULT));
		values.put("lat", (int)(location.getLatitude() * DB_DEGREES_MULT));
		values.put("alt", (int)(location.getAltitude() * DB_METERS_MULT));
		values.put("spd", (int)(location.getAltitude() * DB_METERS_MULT));
		values.put("acc", (int)(location.getAltitude() * DB_METERS_MULT));
		values.put("time", location.getTime());
		return db.insert("trackpoints", null, values);
	}
	
	public long insertWaypoint(String name, String description, String symbol, Location location) {
		stopTransaction();
		if (name == "") {
			SimpleDateFormat formater = new SimpleDateFormat("'Waypoint_'yyyyMMdd_HHmm");
			name = formater.format(location.getTime());
		}
		ContentValues values = new ContentValues();
		values.put("lon", (int)(location.getLongitude() * DB_DEGREES_MULT));
		values.put("lat", (int)(location.getLatitude() * DB_DEGREES_MULT));
		values.put("alt", (int)(location.getAltitude() * DB_METERS_MULT));
		values.put("acc", (int)(location.getAltitude() * DB_METERS_MULT));
		values.put("time", location.getTime());
		values.put("maxzoom", 0);
		values.put("name", name);
		values.put("description", description);
		values.put("symbol", symbol);
		long id = db.insert("waypoints", null, values);
		Log.d(TAG, "waypoint: " + name + " inserted id: " + id);
		return id;
	}
	
	public Cursor getTracksList() {
		stopTransaction();
		String[] columns = new String[]{ "_id", "name", };
		return db.query("tracks", columns, null, null, null, null, "_id DESC");
	}

	public String getTrackName(long id) {
		stopTransaction();
		Cursor c = db.rawQuery("SELECT name FROM tracks WHERE _id = " + id + ";", null);
		if (c.getCount() == 0)
			return "";
		c.moveToFirst();
		if (c.isNull(0))
			return "";
		String name = c.getString(0);
		c.close();
		return name;
	}
	
	public void deleteTrack(long id) {
		stopTransaction();
		db.execSQL("DELETE FROM trackpoints WHERE track_id = " + id + ";");
		db.execSQL("DELETE FROM tracks WHERE _id = " + id + ";");
	}

	public Cursor getWaypointList() {
		stopTransaction();
		String[] columns = new String[]{ "_id", "name", };
		return db.query("waypoints", columns, null, null, null, null, "_id DESC");
	}
	
	public String getWaypointName(long id) {
		stopTransaction();
		Cursor c = db.rawQuery("SELECT name FROM waypoints WHERE _id = " + id + ";", null);
		if (c.getCount() == 0)
			return "";
		c.moveToFirst();
		if (c.isNull(0))
			return "";
		String name = c.getString(0);
		c.close();
		return name;
	}
	
	public void deleteWaypoint(long id) {
		stopTransaction();
		db.execSQL("DELETE FROM waypoints WHERE _id = " + id + ";");
	}

}
